package com.lm.dao.impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.SupporterDAO;
import com.lm.model.Supporter;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;

public class SupporterDAOImpl implements SupporterDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public boolean isExistedUser(String user, String password) throws Throwable {
		boolean flag = false;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_supporter  ");
		sb.append(" where userName like '%").append(user);
		sb.append("%' and password like '%").append(password);
		sb.append("%'");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			if (rs.next()) {
				flag = true;
			}

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return flag;
	}

	public Supporter getUser(String userName) throws Throwable {
		Supporter supporter = new Supporter();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_supporter  where ");
		sb.append(" userName like '%").append(userName).append("%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				supporter.setId(rs.getInt("id"));
				supporter.setAddress(rs.getString("address"));
				supporter.setFax(rs.getString("fax"));
				supporter.setMobile(rs.getString("mobile"));
				supporter.setMsn(rs.getString("msn"));
				supporter.setPassword(rs.getString("password"));
				supporter.setQq(rs.getString("qq"));
				supporter.setTel(rs.getString("tel"));
				supporter.setUserName(rs.getString("userName"));
				supporter.setPicName(rs.getString("picName"));
				supporter.setPicUrl(rs.getString("picURL"));
				supporter.setCompanyUrl(rs.getString("companyURL"));
				supporter.setCompanyDesc(BlobToString((Blob) rs
						.getBlob("companyDesc")));

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return supporter;
	}

	public String getUserNameByUserID(int userID) throws Throwable {
		String userName = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select userName from lm_supporter  where ");
		sb.append(" id =").append(userID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				userName = rs.getString("userName");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return userName;
	}

	public Supporter getSupporterListByID(int userID) throws Throwable {
		Supporter lms = new Supporter();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_supporter  where ");
		sb.append(" id =").append(userID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				lms.setId(rs.getInt("id"));
				lms.setAddress(rs.getString("address"));
				lms.setFax(rs.getString("fax"));
				lms.setMobile(rs.getString("mobile"));
				lms.setMsn(rs.getString("msn"));
				lms.setPassword(rs.getString("password"));
				lms.setQq(rs.getString("qq"));
				lms.setTel(rs.getString("tel"));
				lms.setUserName(rs.getString("userName"));
				lms.setTypeScope(rs.getString("typeScope"));
				lms.setBrandScope(rs.getString("brandScope"));
				lms.setPicName(rs.getString("picName"));
				lms.setPicUrl(rs.getString("picURL"));
				lms.setCompanyUrl(rs.getString("companyURL"));
				lms.setCompanyDesc(BlobToString((Blob) rs
						.getBlob("companyDesc")));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return lms;
	}

	public List getAllSupporter() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_supporter ");
		sb.append(" where userName !='").append("xikaaijia").append("'");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Supporter supporter = new Supporter();
				supporter.setId(rs.getInt("id"));
				supporter.setAddress(rs.getString("address"));
				supporter.setFax(rs.getString("fax"));
				supporter.setMobile(rs.getString("mobile"));
				supporter.setMsn(rs.getString("msn"));
				supporter.setPassword(rs.getString("password"));
				supporter.setQq(rs.getString("qq"));
				supporter.setTel(rs.getString("tel"));
				supporter.setUserName(rs.getString("userName"));
				supporter.setTypeScope(rs.getString("typeScope"));
				supporter.setBrandScope(rs.getString("brandScope"));
				supporter.setPicName(rs.getString("picName"));
				supporter.setPicUrl(rs.getString("picURL"));
				supporter.setCompanyUrl(rs.getString("companyURL"));
				supporter.setCompanyDesc(BlobToString((Blob) rs
						.getBlob("companyDesc")));
				resList.add(supporter);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public void addSupporter(Supporter supporter) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_supporter(userName,password,mobile,");
		sb
				.append(" tel,fax,address,msn,qq,typeScope,brandScope,picURL,picName,companyURL,companyDesc)");
		sb.append(" values('").append(supporter.getUserName()).append("',");
		sb.append("'").append(supporter.getPassword()).append("',");
		sb.append("'").append(supporter.getMobile()).append("',");
		sb.append("'").append(supporter.getTel()).append("',");
		sb.append("'").append(supporter.getFax()).append("',");
		sb.append("'").append(supporter.getAddress()).append("',");
		sb.append("'").append(supporter.getMsn()).append("',");
		sb.append("'").append(supporter.getQq()).append("',");
		sb.append("'").append(supporter.getTypeScope()).append("',");
		sb.append("'").append(supporter.getBrandScope()).append("',");
		sb.append("'").append(supporter.getPicUrl()).append("',");
		sb.append("'").append(supporter.getPicName()).append("',");
		sb.append("'").append(supporter.getCompanyUrl()).append("',");
		sb.append("'").append(supporter.getCompanyDesc()).append("')");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public Supporter getSupporterBySupporterID(int supporterID)
			throws Throwable {
		Supporter supporter = new Supporter();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_supporter  ");
		sb.append(" where id=").append(supporterID);
		sb.append(" and userName !='").append("xikaaijia").append("'");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				supporter.setId(rs.getInt("id"));
				supporter.setAddress(rs.getString("address"));
				supporter.setFax(rs.getString("fax"));
				supporter.setMobile(rs.getString("mobile"));
				supporter.setMsn(rs.getString("msn"));
				supporter.setPassword(rs.getString("password"));
				supporter.setQq(rs.getString("qq"));
				supporter.setTel(rs.getString("tel"));
				supporter.setUserName(rs.getString("userName"));
				supporter.setTypeScope(rs.getString("typeScope"));
				supporter.setBrandScope(rs.getString("brandScope"));
				supporter.setPicName(rs.getString("picName"));
				supporter.setPicUrl(rs.getString("picURL"));
				supporter.setCompanyUrl(rs.getString("companyURL"));
				supporter.setCompanyDesc(BlobToString((Blob) rs
						.getBlob("companyDesc")));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return supporter;
	}

	public void editSupporterBySupportID(Supporter supporter, String supporterID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_supporter set userName='").append(
				supporter.getUserName());
		sb.append("',password='").append(supporter.getPassword());
		sb.append("',mobile='").append(supporter.getMobile());
		sb.append("',tel='").append(supporter.getTel());
		sb.append("',fax='").append(supporter.getFax());
		sb.append("',address='").append(supporter.getAddress());
		sb.append("',msn='").append(supporter.getMsn());
		sb.append("',qq='").append(supporter.getQq());
		sb.append("',typeScope='").append(supporter.getTypeScope()).append("'");
		if (supporter.getPicName() != null) {
			sb.append(",picURL='").append(supporter.getPicUrl()).append("'");
		}
		sb.append(",picName='").append(supporter.getPicName()).append("'");
		sb.append(",brandScope='").append(supporter.getBrandScope()).append("'");
		sb.append(",companyURL='").append(supporter.getCompanyUrl()).append("'");
		sb.append(",companyDesc='").append(supporter.getCompanyDesc()).append("'");
		sb.append(" where id=").append(Integer.parseInt(supporterID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editSupporterPasswordBySupportID(Supporter supporter,
			String supporterID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_supporter set password='").append(
				supporter.getPassword()).append("'");
		sb.append(" where id=").append(Integer.parseInt(supporterID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editPasswordOfAdmin(String password) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_supporter set password='").append(password)
				.append("'");
		sb.append(" where userName ='").append("xikaaijia").append("'");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	private String BlobToString(Blob blob) throws SQLException {
		byte[] bdata = blob.getBytes(1, (int) blob.length());
		String text = new String(bdata);

		return text;
	}

	public void deleteSupporterBySupportID(int supporterID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_supporter");
		sb.append(" where id=").append(supporterID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
